﻿using System;
using System.Net;
using System.Collections.Generic;
using System.Reflection;
using System.Linq;
using System.Text;
using System.Linq.Expressions;

namespace SL8.SL.Data.Helpers
{
    public static class SqlHelper
    {
        public static string ParseToSqlString(object obj)
        {
            if (obj == null)
            {
                return "NULL";
            }

            switch (Type.GetTypeCode(obj.GetType()))
            {
                case TypeCode.Object:
                    throw new Exception("不支持Object类型");
                case TypeCode.Boolean:
                    return string.Format("'{0}'", (bool)obj ? "1" : "0");
                case TypeCode.Char:
                case TypeCode.String:
                    return string.Format("'{0}'", string.Format("{0}", obj).Replace("'", "''"));
                case TypeCode.DateTime:
                    return string.Format("'{0}'", obj);
                case TypeCode.Empty:
                case TypeCode.DBNull:
                    return "NULL";
                default:
                    return obj.ToString();
            }
        }

        public static string GetAddSql(object entity)
        {
            string tableName = MappingHelper.GetMappingTableName(entity.GetType());

            List<string> columns = new List<string>();
            List<object> values = new List<object>();
            List<string> strValues = new List<string>();

            IEnumerable<PropertyInfo> propertyInfos = MappingHelper.GetMappingColumns(entity.GetType());

            columns = propertyInfos.Select(item => item.Name).ToList();

            StringBuilder sb = new StringBuilder();
            sb.Append("INSERT INTO [");
            sb.Append(tableName);
            sb.Append("] (");
            sb.Append(string.Join(",", columns.Select(item => string.Format("[{0}]", item))));
            sb.Append(")");
            sb.Append("VALUES");
            sb.Append("(");

            foreach (var item in propertyInfos)
            {
                string value = SqlHelper.ParseToSqlString(item.GetValue(entity, null));
                strValues.Add(value);
            }

            sb.Append(string.Join(",", strValues.ToArray()));
            sb.Append(")");

            return sb.ToString();
        }

        public static string GetDeleteSql<T>(System.Linq.Expressions.Expression<Func<T, bool>> condition)
        {
            Type type = typeof(T);
            string tableName = MappingHelper.GetMappingTableName(type);

            if (condition == null)
            {
                return string.Format(" DELETE FROM [{0}] ", tableName);
            }
            else
            {
                return string.Format(" DELETE FROM [{0}] WHERE 1=1 AND {1} ", tableName, SqlHelper.GetWhereCondition<T>(condition));
            }
        }

        public static ConditionBuilder conditionBuilder = new ConditionBuilder();

        public static string GetUpdateSql<T>(T t, Expression<Func<T, object>> updater, Expression<Func<T, bool>> condition)
        {
            Type type = typeof(T);
            string tableName = MappingHelper.GetMappingTableName(type);
            List<PropertyInfo> lstProperties = SqlHelper.GetProperties<T>(updater);

            var keyValues = lstProperties.Select(item => new
            {
                Name = item.Name,
                Value = SqlHelper.ParseToSqlString(item.GetValue(t, null))
            });

            string sqlUpdateBlock = string.Join(", ", keyValues.Select(c => string.Format("[{0}]={1}", c.Name, c.Value)).ToArray());

            string strWhere = SqlHelper.GetWhereCondition<T>(condition);

            if (!string.IsNullOrWhiteSpace(strWhere))
            {
                strWhere = " WHERE " + strWhere;
            }

            return string.Format("UPDATE [{0}] SET {1} {2}", tableName, sqlUpdateBlock, strWhere);
        }

        public static List<PropertyInfo> GetProperties<T>(Expression<Func<T, object>> updater)
        {
            List<PropertyInfo> lstProperties = new List<PropertyInfo>();

            if (updater != null && updater.Body != null)
            {
                Type type = typeof(T);
                IEnumerable<PropertyInfo> propertiesTemp = MappingHelper.GetMappingColumns(type);

                var memberExpression = updater.Body as MemberExpression;
                if (memberExpression != null)
                {
                    foreach (var property in propertiesTemp)
                    {
                        if (property.Name == memberExpression.Member.Name)
                        {
                            lstProperties.Add(property);
                            break;
                        }
                    }

                    return lstProperties;
                }

                System.Linq.Expressions.NewExpression newExpression = updater.Body as NewExpression;
                if (newExpression != null)
                {
                    foreach (var member in newExpression.Members)
                    {
                        foreach (var property in propertiesTemp)
                        {
                            if (property.Name == member.Name)
                            {
                                lstProperties.Add(property);
                                break;
                            }
                        }
                    }

                    return lstProperties;
                }
            }

            return lstProperties;
        }

        public static string GetRecordCountSql<T>(System.Linq.Expressions.Expression<Func<T, bool>> condition)
        {
            Type type = typeof(T);
            string tableName = MappingHelper.GetMappingTableName(type);

            if (condition == null)
            {
                return string.Format(" Select Count(*) AS [Count] FROM [{0}]  ", tableName);
            }
            else
            {
                return string.Format(" Select Count(*) AS [Count] FROM [{0}] WHERE 1=1 AND {1} ", tableName, SqlHelper.GetWhereCondition<T>(condition));
            }
        }

        public static string GetWhereCondition<T>(Expression<Func<T, bool>> condition)
        {
            if (condition == null || condition.Body == null)
            {
                return string.Empty;
            }

            Expression expr = Evaluator.PartialEval(condition);
            return SqlHelper.conditionBuilder.Translate(expr);
        }

        public static string GetSearchConditionSql<T>(SearchCondition<T> searchCondition, Expression<Func<T, object>> columns = null)
        {
            if (searchCondition == null)
            {
                searchCondition = new SearchCondition<T>();
            }

            string strKeyColumn = string.Empty,
                strWhere = string.Empty,
                tableName = string.Empty,
                strOrderBy = string.Empty,
                strColumns = "*";

            Type tableType = typeof(T);

            // 列
            List<PropertyInfo> lstProperties = SqlHelper.GetProperties<T>(columns);
            if (lstProperties.Count > 0)
            {
                strColumns = string.Join(", ", lstProperties.Select(item => item.Name).ToArray());
            }

            // where 条件
            strWhere = SqlHelper.GetWhereCondition<T>(searchCondition.ConditionExpression);
            if (!string.IsNullOrWhiteSpace(strWhere))
            {
                strWhere = "  AND " + strWhere;
            }

            // 表名
            tableName = MappingHelper.GetMappingTableName(tableType);

            // 排序
            List<string> orderBys = new List<string>();
            if (searchCondition.SortDescriptions != null && searchCondition.SortDescriptions.Length > 0)
            {
                foreach (var item in searchCondition.SortDescriptions)
                {
                    if (!string.IsNullOrWhiteSpace(item.PropertyName))
                    {
                        orderBys.Add(string.Format("{0}.{1} {2}", tableName, item.PropertyName, (int)item.Direction == 0 ? "ASC" : "DESC"));
                    }
                }

                if (orderBys.Count > 0)
                {
                    strOrderBy = " ORDER BY " + string.Join(",", orderBys.ToArray()) + " ";
                }
            }

            if (searchCondition.PageSize > 0 && searchCondition.PageIndex >= 0)
            {
                //主键 
                PropertyInfo propertyInfo = MappingHelper.GetKeyColumn(tableType);

                if (propertyInfo == null)
                {
                    throw new Exception("找不到表主键,不能进行分页操作");
                }
                else
                {
                    strKeyColumn = propertyInfo.Name;
                }

                if (searchCondition.PageIndex == 0)
                {
                    if (string.IsNullOrWhiteSpace(strOrderBy))
                    {
                        return string.Format(" SELECT TOP {0} {3} FROM {1} WHERE 1=1 {2} ",
                                              searchCondition.PageSize,
                                              tableName,
                                              strWhere,
                                              strColumns);
                    }
                    else
                    {
                        return string.Format(" SELECT TOP {0} {4} FROM (SELECT * FROM {1} {3}) WHERE 1=1 {2}",
                                              searchCondition.PageSize,
                                              tableName,
                                              strWhere,
                                              strOrderBy,
                                              strColumns);
                    }
                }
                else
                {
                    //SELECT TOP 页大小 * FROM TestTable WHERE (ID NOT IN (SELECT TOP 页大小*页数 id FROM 表 ORDER BY id)) ORDER BY ID
                    return string.Format(" SELECT TOP {0} {7} FROM (SELECT * FROM {3} {4}) WHERE ({2} NOT IN (SELECT TOP {6} {2} FROM (SELECT * FROM {3} {4}) WHERE 1=1 {5})) {5} ",
                                        searchCondition.PageSize,
                                        searchCondition.PageIndex,
                                        strKeyColumn,
                                        tableName,
                                        strOrderBy,
                                        strWhere,
                                        searchCondition.PageSize * searchCondition.PageIndex,
                                        strColumns);
                }

            }
            else
            {
                return string.Format("SELECT {3} FROM {0} WHERE 1=1 {1} {2}", tableName, strWhere, strOrderBy, strColumns);
            }
        }
    }
}
